use [db_iChat]
go
create procedure pro_selectRemarksByArticleID
    @articleID int
    as
    select * from tb_remarks where articleID = @articleID
    
    go
create procedure pro_selectRemarksByAutherID
    @autherID int
    as
    select * from tb_remarks where autherID = @autherID

go
create procedure pro_insertRemarks
	 @remarkID   int   ,
	 @articleID   int   ,
	 @autherID   int   ,
	 @remarkTitle   nchar (200)  ,
	 @createTime   smalldatetime   ,
	 @ReplyID   int   ,
	 @remarkUrl   nchar (200)  ,
	 @remarkPath  nchar (200)  ,
	 @up   int   ,
	 @down   int 
	 as
	 insert into tb_remarks values(	
	  @remarkID   ,
	 @articleID      ,
	 @autherID    ,
	 @remarkTitle    ,
	 @createTime  ,
	 @ReplyID     ,
	 @remarkUrl   ,
	 @remarkPath   ,
	 @up  ,
	 @down )

go
create procedure pro_updateRemarks
	 @remarkID   int   ,
	 @articleID   int   ,
	 @autherID   int   ,
	 @remarkTitle   nchar (200)  ,
	 @createTime   smalldatetime   ,
	 @ReplyID   int   ,
	 @remarkUrl   nchar (200)  ,
	 @remarkPath  nchar (200)  ,
	 @up   int   ,
	 @down   int 
	 as
	 update tb_remarks 
	 set
	 remarkID  =@remarkID   ,
	 articleID =@articleID      ,
	 autherID = @autherID    ,
	 remarkTitle=@remarkTitle   ,
	 createTime=@createTime  ,
	 ReplyID=@ReplyID     ,
	 remarkUrl =@remarkUrl   ,
	 remarkPath =@remarkPath   ,
	 up =@up  ,
	 down=@down 
	 where 
	 remarkID =@remarkID 
	 
go
create procedure pro_deleteRemarksByRemarkID
    @remarkID int
    as
    delete from tb_remarks where remarkID=@remarkID
    
    go
create procedure pro_deleteRemarksByAutherID
    @autherID int
    as
    delete from tb_remarks where remarkID=@autherID


go
create procedure pro_selectUsersByName
    @name nchar(20)
    as
    select * from tb_users where name=@name
    
    go
create procedure pro_selectUsersByID
    @ID int
    as
    select * from tb_users where ID=@ID
    
    go
    create procedure pro_selectUsersNumber
    as
    select count(*) from tb_users

go
create procedure pro_insertUsers
     @ID   int   ,
	 @name   nchar (20)  ,
	 @hashedPassword   binary (16)  ,
	 @question   nchar (200)  ,
	 @hashedAnswer   binary (16)  ,
	 @email   nchar (200)  ,
	 @sighWords   nchar (200)  ,
	 @sex   nchar(1)   ,
	 @msgGotRecordsXmlPath   nchar (200)  ,
	 @msgSentRecordsXmlPath   nchar (200)  ,
	 @friendsListXmlPath nchar(200)  ,
	 @address   nchar (200)  ,
	 @imgHeadUrl   nchar (200)  ,
	 @imgHeadPath nchar(200) ,
	 @regTime   smalldatetime   ,
	 @lastLogTime    smalldatetime   ,
	 @logTimes  int   ,
	 @points   int   ,
	 @roles   int   ,
	 @phone   nchar (20) ,
	 @leaveTime smalldatetime ,
	 @onlineTime float,
	 @systemInfoXmlPath nchar(200)
as
insert into tb_users values(@ID,
     @name,
     @hashedPassword,
     @question,
     @hashedAnswer,
     @email,
     @sighWords,
     @sex,
     @msgGotRecordsXmlPath,
     @msgSentRecordsXmlPath,
     @friendsListXmlPath,
     @address,
     @imgHeadUrl,
     @imgHeadPath,
     @regTime,
     @lastLogTime,
     @logTimes,
     @points,
     @roles,
     @phone,
     @leaveTime,
     @onlineTime,
     @systemInfoXmlPath
     )

go
create procedure pro_updateUsers
     @ID   int   ,
	 @name   nchar (20)  ,
	 @hashedPassword   binary (16)  ,
	 @question   nchar (200)  ,
	 @hashedAnswer   binary (16)  ,
	 @email   nchar (200)  ,
	 @sighWords   nchar (200)  ,
	 @sex   nchar(1)   ,
	 @msgGotRecordsXmlPath   nchar (200)  ,
	 @msgSentRecordsXmlPath   nchar (200)  ,
	 @friendsListXmlPath nchar(200)  ,
	 @address   nchar (200)  ,
	 @imgHeadUrl   nchar (200)  ,
	 @imgHeadPath nchar(200) ,
	 @regTime   smalldatetime   ,
	 @lastLogTime   smalldatetime   ,
	 @logTimes   int   ,
	 @points   int   ,
	 @roles   int   ,
	 @phone   nchar (20) ,
	 @leaveTime smalldatetime ,
	 @onlineTime float,
	 @systemInfoXmlPath nchar(200)
	 
	 as
	 update tb_users
	 set 
	 ID=@ID,
	 name=@name,
	 hashedPassword=@hashedPassword,
	 question=@question,
	 hashedAnswer=@hashedAnswer,
	 email=@email,
	 sighWords=@sighWords,
	 sex=@sex,
	 msgGotRecordsXmlPath=@msgGotRecordsXmlPath,
	 msgSentRecordsXmlPath=@msgSentRecordsXmlPath,
	 friendsListXmlPath=@friendsListXmlPath,
	 address=@address,
	 imgHeadUrl=@imgHeadUrl,
	 imgHeadPath=@imgHeadPath,
	 regTime=@regTime,
	 logTimes=@logTimes,
	 lastLogTime=@lastLogTime,
	 points=@points,
	 roles=@roles,
	 phone=@phone,
	leaveTime= @leaveTime ,
	 onlineTime=@onlineTime ,
	 systemInfoXmlPath=@systemInfoXmlPath
	 where ID=@ID
	 

go
create procedure pro_deleteUsersByName
    @name nchar(20)
    as
    delete from tb_users
    where name = @name
    
    go
    create procedure pro_deleteUsersByID
    @ID int
    as
    delete from tb_users
    where ID = @ID

go
create procedure pro_selectArticlesByAutherID
    @autherID int
    as
    select * from tb_articles 
    where autherID = @autherID
    
    go
create procedure pro_selectArticlesByArticleID
    @articleID int
    as
    select * from tb_articles 
    where autherID = @articleID

go
create procedure pro_insertArticles
	 @articleID   int   ,
	 @articleTitle   nchar (200)  ,
	 @articleUrl   nchar (200)  ,
	 @articlePath   nchar (200)  ,
	 @autherID   nchar (20)  ,
	 @createTime   smalldatetime   ,
	 @clickTimes   int   ,
	 @topic   nchar (10)  ,
	 @type   nchar (10)  ,
	 @up   int   ,
	 @down   int 
	as
	insert into tb_articles values(
	 @articleID    ,
	 @articleTitle     ,
	 @articleUrl     ,
	 @articlePath   ,
	 @autherID     ,
	 @createTime     ,
	 @clickTimes     ,
	 @topic  ,
	 @type   ,
	 @up   ,
	 @down)
	 
go
create procedure pro_updateArticles
	 @articleID   int   ,
	 @articleTitle   nchar (200)  ,
	 @articleUrl   nchar (200)  ,
	 @articlePath   nchar (200)  ,
	 @autherID   nchar (20)  ,
	 @createTime   smalldatetime   ,
	 @clickTimes   int   ,
	 @topic   nchar (10)  ,
	 @type   nchar (10)  ,
	 @up   int   ,
	 @down   int 
	as
	update tb_articles
	set
	 articleID=@articleID    ,
	 articleTitle=@articleTitle     ,
	 articleUrl =@articleUrl    ,
	 articlePath=@articlePath   ,
	 autherID=@autherID     ,
	 createTime=@createTime     ,
	 clickTimes=@clickTimes     ,
	 topic =@topic  ,
	 type=@type   ,
	 up=@up   ,
	 down =@down   

go
create procedure pro_deleteArticlesByArticleID
    @articleID int
    as
    delete from tb_articles where articleID = @articleID
    
    go
create procedure pro_deleteArticlesByAutherID
    @autherID  int
    as
    delete from tb_articles where autherID = @autherID
